import os
import sys
import shutil
import sqlite3
from datetime import datetime

from xml.etree.ElementTree import Element, SubElement, tostring
from xml.dom import minidom

# PyQt5 is widely available; switch to PyQt6 if preferred
from PyQt5.QtWidgets import (
    QApplication,
    QMainWindow,
    QWidget,
    QVBoxLayout,
    QHBoxLayout,
    QLabel,
    QLineEdit,
    QPushButton,
    QFileDialog,
    QTextEdit,
    QCheckBox,
    QProgressBar,
    QMessageBox,
)
from PyQt5.QtCore import Qt, QThread, pyqtSignal


ROM_EXTS = {"iso", "cso", "gba", "zip", "7z", "chd", "nes", "sfc", "smc", "nds"}
IMAGE_EXTS = {"jpg", "jpeg", "png"}


def ts():
    return datetime.now().strftime("%Y%m%d-%H%M%S")


def ensure_readable_dir(path):
    try:
        with os.scandir(path) as it:
            for _ in it:
                break
        return True
    except Exception:
        return False


def ensure_writable_dir(path):
    try:
        test = os.path.join(path, f".__perm_test__{ts()}")
        with open(test, "wb") as f:
            f.write(b"ok")
        os.remove(test)
        return True
    except Exception:
        return False


def ensure_readable_file(path):
    try:
        with open(path, "rb"):
            pass
        return True
    except Exception:
        return False


def backup_file(path, log):
    if os.path.exists(path):
        if not ensure_readable_file(path):
            log(f"备份跳过(不可读): {path}")
            return
        dst_dir = os.path.dirname(path) or "."
        if not ensure_writable_dir(dst_dir):
            log(f"备份跳过(不可写): {dst_dir}")
            return
        dst = f"{path}.bak-{ts()}"
        try:
            shutil.copy2(path, dst)
            log(f"已备份: {path} -> {dst}")
        except Exception as e:
            log(f"备份失败 {path}: {e}")


def is_rom_file(filename):
    ext = os.path.splitext(filename)[1].lower().lstrip(".")
    return ext in ROM_EXTS


def is_image_file(filename):
    ext = os.path.splitext(filename)[1].lower().lstrip(".")
    return ext in IMAGE_EXTS


def rel_from(root, path):
    rel = os.path.relpath(path, root)
    return "./" + rel.replace("\\", "/")


def build_image_index(category_root):
    index = {}
    for dirpath, _dirnames, filenames in os.walk(category_root):
        for filename in filenames:
            if not is_image_file(filename):
                continue
            base = os.path.splitext(filename)[0]
            abs_path = os.path.join(dirpath, filename)
            index.setdefault(base, []).append(abs_path)
    return index


def choose_image_for_rom(category_root, rom_dir, rom_base, image_index):
    images = image_index.get(rom_base)
    if not images:
        return None
    # 优先同目录
    for p in images:
        if os.path.dirname(p) == rom_dir:
            return rel_from(category_root, p)
    # 否则选第一个
    return rel_from(category_root, images[0])


def prettify_xml(elem):
    # 直接在内存中美化，避免临时文件
    xml_bytes = tostring(elem, encoding="utf-8")
    parsed = minidom.parseString(xml_bytes)
    pretty = parsed.toprettyxml(indent="\t", encoding="utf-8")
    return pretty


def generate_gamelist_xml(category_root, category_name, games, log):
    xml_root = Element("gameList")
    for g in games:
        gameid = g.get("gameid") or "00000"
        game = SubElement(xml_root, "game")
        SubElement(game, "gameid").text = gameid
        SubElement(game, "path").text = g["path"]
        if g.get("image"):
            SubElement(game, "image").text = g["image"]
        else:
            SubElement(game, "image").text = ""
        SubElement(game, "video_id").text = "0"
        SubElement(game, "class_type").text = "0"
        SubElement(game, "game_type").text = "0"
        SubElement(game, "timer").text = category_name
        # 名称：示例同PRD，填充中文与name相同
        display_name = f"{gameid} {g['title']}"
        SubElement(game, "zh_CN").text = display_name
        SubElement(game, "name").text = display_name

    target = os.path.join(category_root, "gamelist.xml")
    backup_file(target, log)

    # 写入XML
    try:
        # 使用minidom进行缩进美化（包含xml声明）
        xml_bytes = prettify_xml(xml_root)
        with open(target, "wb") as f:
            f.write(xml_bytes)
        log(f"生成 gamelist.xml: {target}")
    except Exception as e:
        log(f"写入XML失败: {e}")


def scan_category(category_root, log):
    if not ensure_readable_dir(category_root):
        log(f"目录不可读，跳过: {category_root}")
        return []
    image_index = build_image_index(category_root)
    games = []
    for dirpath, _dirnames, filenames in os.walk(category_root):
        for filename in filenames:
            if not is_rom_file(filename):
                continue
            full_path = os.path.join(dirpath, filename)
            base = os.path.splitext(filename)[0]
            rel_rom = rel_from(category_root, full_path)
            image_rel = choose_image_for_rom(category_root, dirpath, base, image_index)
            games.append({
                "path": rel_rom,
                "image": image_rel,
                "title": base,
            })
    # 按路径排序，稳定输出
    games.sort(key=lambda g: g["path"].lower())
    log(f"扫描到 {len(games)} 个ROM: {category_root}")
    return games


def update_categ_totals_if_possible(root_dir, totals_map, log):
    db_path = os.path.join(root_dir, "cachegames.db")
    if not os.path.exists(db_path):
        log("未找到 cachegames.db，跳过数据库更新。")
        return
    backup_file(db_path, log)
    try:
        conn = sqlite3.connect(db_path)
        cur = conn.cursor()
        # 检查categ表
        cur.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='categ'")
        if cur.fetchone() is None:
            log("数据库中不存在 categ 表，跳过更新。")
            conn.close()
            return

        # 确认列是否存在（name/total）
        cur.execute("PRAGMA table_info(categ)")
        cols = {row[1] for row in cur.fetchall()}
        if not {"name", "total"}.issubset(cols):
            log("categ 表缺少 name/total 列，跳过更新。")
            conn.close()
            return

        for name, total in totals_map.items():
            cur.execute("SELECT COUNT(*) FROM categ WHERE name=?", (name,))
            exists = cur.fetchone()[0] > 0
            if exists:
                cur.execute("UPDATE categ SET total=? WHERE name=?", (total, name))
                log(f"更新 categ.total: {name} -> {total}")
            else:
                # 插入新分类，sort默认0
                cur.execute("INSERT INTO categ(name, total, sort) VALUES(?, ?, 0)", (name, total))
                log(f"新增分类并设置 total: {name} -> {total}")
        conn.commit()
        conn.close()
    except Exception as e:
        log(f"数据库更新失败: {e}")


def normalize_for_db(rel_path):
    if not rel_path:
        return None
    # 去掉前导 ./ 或 .\ 并统一分隔符
    if rel_path.startswith("./"):
        rel_path = rel_path[2:]
    if rel_path.startswith(".\\"):
        rel_path = rel_path[3:]
    return rel_path.replace("\\", "/")


def rebuild_game_table(root_dir, all_games, totals_map, log):
    db_path = os.path.join(root_dir, "cachegames.db")
    if not os.path.exists(db_path):
        log("未找到 cachegames.db，无法重建 game 表。")
        return
    if not ensure_readable_file(db_path):
        log("数据库文件不可读，跳过重建 game 表。")
        return
    if not ensure_writable_dir(os.path.dirname(db_path) or "."):
        log("数据库目录不可写，跳过重建 game 表。")
        return

    backup_file(db_path, log)

    try:
        conn = sqlite3.connect(db_path)
        cur = conn.cursor()

        # 检查或创建 categ 表（如果缺失则先创建以便拿到分类id）
        cur.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='categ'")
        if cur.fetchone() is None:
            log("数据库不存在 categ 表，创建一个空的 categ 表。")
            cur.execute(
                "CREATE TABLE IF NOT EXISTS categ (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, total INTEGER, sort INTEGER DEFAULT 0)"
            )
            conn.commit()

        # 确保 totals 已写入 categ，并建立 name->id 映射
        # 先写入/更新分类条目
        for name, total in totals_map.items():
            cur.execute("SELECT id FROM categ WHERE name=?", (name,))
            row = cur.fetchone()
            if row:
                cur.execute("UPDATE categ SET total=? WHERE id=?", (total, row[0]))
            else:
                cur.execute("INSERT INTO categ(name, total, sort) VALUES(?, ?, 0)", (name, total))
        conn.commit()

        # 重新取得分类映射
        cur.execute("SELECT id, name FROM categ")
        name_to_id = {row[1]: row[0] for row in cur.fetchall()}

        # 重建 game 表
        cur.execute("DROP TABLE IF EXISTS game")
        cur.execute(
            """
            CREATE TABLE game (
                idx INTEGER PRIMARY KEY AUTOINCREMENT,
                id INTEGER,
                gameid TEXT,
                path TEXT,
                image TEXT,
                class_type INTEGER DEFAULT 0,
                game_type INTEGER DEFAULT 0,
                zh_CN TEXT,
                en_US TEXT,
                zh_TW TEXT,
                ko_KR TEXT,
                name TEXT,
                fav INTEGER DEFAULT 0,
                gnum TEXT,
                his INTEGER DEFAULT 0,
                sort INTEGER DEFAULT 0
            )
            """
        )

        # 批量插入
        idx_val = 1
        insert_sql = (
            "INSERT INTO game (idx, id, gameid, path, image, class_type, game_type, zh_CN, en_US, zh_TW, ko_KR, name, fav, gnum, his, sort) "
            "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
        )

        for g in all_games:
            cat = g.get("category")
            cat_id = name_to_id.get(cat)
            if cat_id is None:
                # 若分类不存在映射，跳过该条并记录
                log(f"分类未找到id，跳过: {cat}")
                continue
            gameid = g.get("gameid") or f"{idx_val:05d}"
            gnum = "0" + gameid  # 视作在 gameid 前加一个 0

            path_db = normalize_for_db(g.get("path"))
            image_db = normalize_for_db(g.get("image"))

            title = g.get("title") or ""
            display_name = f"{gameid} {title}" if title else gameid

            cur.execute(
                insert_sql,
                (
                    idx_val,
                    cat_id,
                    gameid,
                    path_db,
                    image_db,
                    0,  # class_type
                    0,  # game_type
                    display_name,  # zh_CN
                    None,  # en_US
                    None,  # zh_TW
                    None,  # ko_KR
                    title,  # name 使用原始标题
                    0,  # fav
                    gnum,
                    0,  # his
                    0,  # sort
                ),
            )
            idx_val += 1

        # 同步更新 sqlite_sequence 的 game 条目为当前总记录数
        total_inserted = idx_val - 1
        try:
            cur.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='sqlite_sequence'")
            if cur.fetchone() is not None:
                cur.execute("SELECT seq FROM sqlite_sequence WHERE name='game'")
                row = cur.fetchone()
                if row is None:
                    cur.execute("INSERT INTO sqlite_sequence(name, seq) VALUES(?, ?)", ("game", total_inserted))
                else:
                    cur.execute("UPDATE sqlite_sequence SET seq=? WHERE name='game'", (total_inserted,))
                log(f"更新 sqlite_sequence: game -> {total_inserted}")
            else:
                log("sqlite_sequence 表不存在，跳过更新。")
        except Exception as e:
            log(f"更新 sqlite_sequence 失败: {e}")

        conn.commit()
        conn.close()
        log(f"已重建 game 表，插入 {total_inserted} 条记录。")
    except Exception as e:
        log(f"重建 game 表失败: {e}")


class ScanWorker(QThread):
    sig_log = pyqtSignal(str)
    sig_progress = pyqtSignal(int)
    sig_done = pyqtSignal(dict, int)
    sig_error = pyqtSignal(str)

    def __init__(self, root_dir, update_db):
        super().__init__()
        self.root_dir = root_dir
        self.update_db = update_db

    def log(self, msg):
        self.sig_log.emit(msg)

    def run(self):
        try:
            roms_dir = os.path.join(self.root_dir, "roms")
            if not os.path.isdir(roms_dir):
                self.sig_error.emit("所选目录下未找到 roms/。")
                return
            if not ensure_readable_dir(roms_dir):
                self.sig_error.emit("roms/ 目录不可读。")
                return

            categories = sorted([d for d in os.listdir(roms_dir) if os.path.isdir(os.path.join(roms_dir, d))])
            if not categories:
                self.log("未发现任何分类目录。")
                self.sig_done.emit({}, 0)
                return

            totals_map = {}
            total_files = 0
            global_idx = 1
            all_games = []
            for i, cat in enumerate(categories, start=1):
                cat_root = os.path.join(roms_dir, cat)
                if not ensure_readable_dir(cat_root):
                    self.log(f"分类不可读，跳过: {cat}")
                    continue
                games = scan_category(cat_root, self.log)
                # 赋予全局 gameid，稳定排序后顺序分配
                for g in games:
                    g["gameid"] = f"{global_idx:05d}"
                    global_idx += 1
                total_files += len(games)
                totals_map[cat] = len(games)
                for g in games:
                    gg = dict(g)
                    gg["category"] = cat
                    all_games.append(gg)

                if not ensure_writable_dir(cat_root):
                    self.log(f"分类不可写，跳过XML生成: {cat_root}")
                else:
                    generate_gamelist_xml(cat_root, cat, games, self.log)

                # 进度：按分类粗略估算；主线程UI保持响应
                p = int(i / max(1, len(categories)) * 100)
                self.sig_progress.emit(p)

            if self.update_db:
                update_categ_totals_if_possible(self.root_dir, totals_map, self.log)
                rebuild_game_table(self.root_dir, all_games, totals_map, self.log)
            else:
                self.log("已跳过数据库更新（未勾选）。")

            self.sig_done.emit(totals_map, total_files)
        except Exception as e:
            self.sig_error.emit(str(e))


class MainWindow(QMainWindow):
    def __init__(self):
        super().__init__()
        self.setWindowTitle("M88 游戏列表生成器（原型）")
        self.resize(800, 600)

        cw = QWidget(self)
        self.setCentralWidget(cw)
        v = QVBoxLayout(cw)

        # 选择目录
        row = QHBoxLayout()
        v.addLayout(row)
        row.addWidget(QLabel("根目录:"))
        self.path_edit = QLineEdit()
        self.path_edit.setPlaceholderText("请选择包含 cachegames.db 与 roms/ 的目录")
        row.addWidget(self.path_edit)
        browse_btn = QPushButton("浏览…")
        browse_btn.clicked.connect(self.on_browse)
        row.addWidget(browse_btn)

        # 选项
        opt_row = QHBoxLayout()
        v.addLayout(opt_row)
        self.update_db_chk = QCheckBox("更新数据库(categ.total)")
        self.update_db_chk.setChecked(False)
        opt_row.addWidget(self.update_db_chk)

        # 控制按钮与进度
        ctrl_row = QHBoxLayout()
        v.addLayout(ctrl_row)
        self.start_btn = QPushButton("开始生成")
        self.start_btn.clicked.connect(self.on_start)
        ctrl_row.addWidget(self.start_btn)
        self.progress = QProgressBar()
        self.progress.setRange(0, 100)
        ctrl_row.addWidget(self.progress)

        # 日志
        v.addWidget(QLabel("日志:"))
        self.log_view = QTextEdit()
        self.log_view.setReadOnly(True)
        v.addWidget(self.log_view)

    def log(self, msg):
        self.log_view.append(msg)
        # 让UI及时刷新
        QApplication.processEvents()

    def on_browse(self):
        d = QFileDialog.getExistingDirectory(self, "选择根目录", os.getcwd())
        if d:
            self.path_edit.setText(d)

    def on_start(self):
        root_dir = self.path_edit.text().strip()
        if not root_dir:
            QMessageBox.warning(self, "提示", "请先选择根目录。")
            return
        # 禁用控件并显示忙碌进度
        self.start_btn.setEnabled(False)
        self.progress.setRange(0, 0)  # 忙碌指示，避免未响应
        self.log("开始后台扫描…")

        self.worker = ScanWorker(root_dir, self.update_db_chk.isChecked())
        self.worker.sig_log.connect(self.on_worker_log)
        self.worker.sig_progress.connect(self.on_worker_progress)
        self.worker.sig_done.connect(self.on_worker_done)
        self.worker.sig_error.connect(self.on_worker_error)
        self.worker.start()

    def on_worker_log(self, msg):
        self.log(msg)

    def on_worker_progress(self, p):
        # 切换为确定进度
        if self.progress.minimum() == 0 and self.progress.maximum() == 0:
            self.progress.setRange(0, 100)
        self.progress.setValue(p)

    def on_worker_done(self, totals_map, total_files):
        self.progress.setValue(100)
        self.log(f"完成。分类数: {len(totals_map)}, 总ROM数: {total_files}")
        self.start_btn.setEnabled(True)

    def on_worker_error(self, err):
        QMessageBox.warning(self, "错误", err)
        self.start_btn.setEnabled(True)
        self.progress.setRange(0, 100)
        self.progress.setValue(0)


def main():
    app = QApplication(sys.argv)
    w = MainWindow()
    w.show()
    sys.exit(app.exec_())


if __name__ == "__main__":
    main()